﻿<%@ WebHandler Language="C#" Class="ZSWorkZoneLayoutDashBoard" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;

/*
 * Description: 装配厂--车间工位布局图
 * Copyright(c): 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2023.9.20
 */

public class ZSWorkZoneLayoutDashBoard : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getDetail":
                        r = getDetail(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    Result getData(JObject jObject)
    {
        string sql = @"
SELECT ep.machineno,wf.description,rd.workstationname,ep.workflowstepname,ep.containerno,ep.plannedfinishdate,ep.finishdate,
       CASE WHEN ep.finishdate IS NULL AND SYSDATE > ep.plannedfinishdate THEN '超期未完成'
            WHEN ep.finishdate IS NULL AND SYSDATE <= ep.plannedfinishdate THEN '其他'
            WHEN ep.finishdate IS NOT NULL AND ep.finishdate > ep.plannedfinishdate THEN '超期完成'
            WHEN ep.finishdate IS NOT NULL AND ep.finishdate <= ep.plannedfinishdate THEN '按时完成'
        END finishstatus
FROM apsplaninfo ai
LEFT JOIN apsplandetail aps ON aps.apsplaninfoid = ai.apsplaninfoid AND aps.isuse=0
LEFT JOIN workstation rd ON rd.workstationid = aps.workstationid --工位
LEFT JOIN enginepartplan ep ON ep.concontainerid = ai.containerid
LEFT JOIN containercurrentinfo cci ON cci.containerid = ai.containerid
LEFT JOIN workflow wf ON wf.workflowid = cci.workflowid
LEFT JOIN factory f ON f.factoryid = aps.specfactoryid
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
LEFT JOIN orderstatus od ON od.orderstatusid = mfg.orderstatusid
WHERE ai.apstype2 = 2 AND rd.workstationid IS NOT NULL 
      AND aps.status IN (2,1) 
      AND f.factoryname = '{0}'
      AND ep.workflowstepname IN ('0020-部装','0030-传装','0040-总装')
      AND NVL(od.orderstatusname,'打开') <> '关闭'
GROUP BY ep.machineno,wf.description,rd.workstationname,ep.containerno,ep.workflowstepname,ep.plannedfinishdate,ep.finishdate";
        sql = string.Format(sql,jObject["factoryname"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result getDetail(JObject jObject)
    {
        string sql = @"
SELECT ep.machineno,ep.workflowstepname,
       cci.containerid,ep.containerno,wf.description,ep.plannedstartdate,ep.plannedfinishdate,ep.usecompleted,ep.finishdate,
       DECODE(ep.icfullsetrate,1,'是','否') isqitao,wd.shiftname,csd.teamname,e.fullname,
       CASE WHEN db.dealfaultbillid IS NOT NULL THEN '是' ELSE '否' END isdealfault
FROM enginepartplan ep
LEFT JOIN containercurrentinfo cci ON ep.concontainerid = cci.containerid
LEFT JOIN workflow wf ON wf.workflowid = cci.workflowid
LEFT JOIN (
     SELECT wd.workstationname,s.shiftname
     FROM workstation wd
     LEFT JOIN shift s ON s.shiftid = wd.shiftid
     WHERE wd.workstationname = '{0}'
) wd ON 1=1
LEFT JOIN (
     SELECT csd.containerid,t.teamname,row_number() OVER(PARTITION BY csd.containerid,t.teamname ORDER BY csd.createdate DESC) seq 
     FROM containerstepdispatch csd
     LEFT JOIN team t ON t.teamid = csd.teamid
     LEFT JOIN containercurrentinfo cci ON cci.containerid = csd.containerid
     WHERE cci.containerno = '{1}'
) csd ON csd.containerid = ep.containerid AND csd.seq = 1
LEFT JOIN (
     SELECT csd.containerid,e.fullname,csd.recvdate,ROWNUM seq
     FROM containerstepdispatch csd
     LEFT JOIN employee e ON e.employeeid = csd.recvemployeeid
     LEFT JOIN containercurrentinfo cci ON cci.containerid = csd.containerid
     WHERE cci.containerno = '{1}' AND csd.recvdate IS NOT NULL
     ORDER BY csd.recvdate
) e ON e.containerid = ep.containerid AND e.seq = 1
LEFT JOIN (
     SELECT ep.containername,ep.containerno,ep.workflowstepname,ep.bigsmallbomid,pb.productname,db.transferstatus,db.dealfaultbillid
     FROM enginepartplan ep
     LEFT JOIN productmateriallistitem pm ON pm.bomid = ep.bigsmallbomid
     LEFT JOIN product p ON p.productid = pm.productid
     LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
     LEFT JOIN dealfaultbill db ON db.productname = pb.productname AND db.transferstatus NOT IN (5,6,7)
     WHERE db.dealfaultbillid IS NOT NULL AND ep.workflowstepname IN ('0020-部装','0030-传装','0040-总装') 
           AND ep.containerno = '{1}'
) db ON db.containerno = ep.containerno
WHERE ep.containerno = '{1}' AND ep.workflowstepname IN ('0020-部装','0030-传装','0040-总装')";
        sql = string.Format(sql, jObject["workstationname"].ToString(), jObject["containerno"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }
}